package attend.dao.codetable;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import attend.com.CodeMetrix;

import util.DBConnection;

import exception.BaseException;

public class CodetableDao extends DBConnection {

	private Connection			con			=	null;
	private PreparedStatement	pstmt		= 	null;
	private ResultSet			rs			= 	null;
	
	private StringBuffer		strBuff		= 	new StringBuffer();

	int					result		= 0;

	private List<HashMap<String, String>> list = null;

	private int			searchResult = 0;
	private String		groupCode;
	private String		group_cd;
	private String		cd;
	private String		use_yn;
	private int			rowCount;
	private int			count;
	private String 		msg;
	
	
	/**
	 * @param checkedGroupCode
	 * @param full_nm
	 * @throws BaseException
	 */
	public void search_tot(String checkedGroupCode, String full_nm) throws BaseException {
		
		list = new ArrayList<HashMap<String, String>>();

		try {
			
			con = getDataSource().getConnection();
			
			
			strBuff.setLength(0);
			
			strBuff.append("			SELECT                                            					\r\n");
			strBuff.append("					COUNT(*) AS ROWNUM                        					\r\n");
			strBuff.append("			FROM                                              					\r\n");
			strBuff.append("					TB_COMM_CD A,                             					\r\n");
			strBuff.append("					(                                         					\r\n");
			strBuff.append("						SELECT                                					\r\n");
			strBuff.append("								GROUP_CD, CD_NM AS GROUP_CD_NM					\r\n");
			strBuff.append("						FROM                                  					\r\n");
			strBuff.append("								TB_COMM_CD                    					\r\n");
			strBuff.append("						WHERE                                 					\r\n");
			strBuff.append("								CD = '0'                      					\r\n");
			strBuff.append("					) B                                       					\r\n");
			strBuff.append("			 WHERE	1=1                                       					\r\n");
			strBuff.append("				AND		A.GROUP_CD = B.GROUP_CD               					\r\n");
			
			if (!checkedGroupCode.equals("all")) {
				strBuff.append(" AND A.GROUP_CD = '" + checkedGroupCode + "'                \r\n");
			}
			
			if (!full_nm.equals("")) {
				strBuff.append(" AND A.CD_NM LIKE '%"+full_nm+"%'                			\r\n");
			}
			
//			System.out.println("-----------------------------------------------------------------");
//			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		S	T	A	R	T");
//			System.out.println("-----------------------------------------------------------------");
//			System.out.println("Search_tot Query	\r\n" + strBuff.toString());
//			System.out.println("-----------------------------------------------------------------");
//			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		E	N	D	T");
//			System.out.println("-----------------------------------------------------------------");

			pstmt = con.prepareStatement(strBuff.toString());

			rs = pstmt.executeQuery();

			while (rs.next()) {
				rowCount = rs.getInt("ROWNUM");
			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();

			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}
	
	/**
	 * @param chec                
	 * @param full_nm
	 * @param start_row
	 * @param end_row
	 * @throws BaseException
	 */
	public void search(String checkedGroupCode, String full_nm, int start_row, int end_row) throws BaseException {
		list = new ArrayList<HashMap<String, String>>();
		try {

			con = getDataSource().getConnection();
			
			strBuff.setLength(0);

			strBuff.append("		SELECT AA.*                                                 		\r\n");
			strBuff.append("		FROM (																\r\n");
			strBuff.append("			SELECT                                                  		\r\n");
			strBuff.append("				ROW_NUMBER() OVER(ORDER BY A.GROUP_CD , A.CD ) AS ROWNUM ,  \r\n");
			strBuff.append("				A.GROUP_CD,													\r\n");
			strBuff.append("				B.GROUP_CD_NM,												\r\n");
			strBuff.append("				A.CD,														\r\n");
			strBuff.append("				A.CD_NM,													\r\n");
			strBuff.append("				A.ETC,														\r\n");
			strBuff.append("				A.USE_YN													\r\n");
			strBuff.append("			FROM TB_COMM_CD A,												\r\n");
			strBuff.append("				(															\r\n");
			strBuff.append("					SELECT GROUP_CD, CD_NM AS GROUP_CD_NM         			\r\n");
			strBuff.append("					FROM TB_COMM_CD                                 		\r\n");
			strBuff.append("					WHERE CD = '0'                                  		\r\n");
			strBuff.append("				) B															\r\n");
			strBuff.append("			WHERE 1=1														\r\n");
			strBuff.append("			AND A.GROUP_CD = B.GROUP_CD										\r\n");
			
			if (!checkedGroupCode.equals("all")) {
				strBuff.append(" AND A.GROUP_CD = '" + checkedGroupCode + "'                			\r\n");
			}
			
			if (!full_nm.equals("")) {
				strBuff.append(" AND A.CD_NM LIKE '%"+full_nm+"%'                						\r\n");
			}
			
			strBuff.append(" ) AA  WHERE ROWNUM BETWEEN "+start_row+" AND "+end_row);
			

			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		S	T	A	R	T");
			System.out.println("-----------------------------------------------------------------");
			System.out.println("Search Query							\r\n" + strBuff.toString());
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		E	N	D	T");
			System.out.println("-----------------------------------------------------------------");
			
			pstmt = con.prepareStatement(strBuff.toString());

			rs = pstmt.executeQuery();

			while (rs.next()) {

				searchResult++;

				HashMap<String, String> hashtable = new HashMap<String, String>();

				String table = rs.getString("GROUP_CD") + "," + rs.getString("CD");

				hashtable.put("table"			, table);
				hashtable.put("group_cd"		, rs.getString("GROUP_CD"));
				hashtable.put("group_code_nm"	, rs.getString("GROUP_CD_NM"));
				hashtable.put("cd"				, rs.getString("CD"));
				hashtable.put("full_nm"			, rs.getString("CD_NM"));
				hashtable.put("etc"				, rs.getString("ETC"));
				hashtable.put("use_yn"			, rs.getString("USE_YN"));

				list.add(hashtable);
			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();

			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}

	/**
	 * @throws BaseException
	 */
	public void getGroupCodeName() throws BaseException {

		list = new ArrayList<HashMap<String, String>>();

		try {

			con = getDataSource().getConnection();
			
			strBuff.setLength(0);
			
			strBuff.append("		SELECT	            			\r\n");
			strBuff.append("				GROUP_CD				\r\n");
			strBuff.append("			,	CD_NM					\r\n");
			strBuff.append("		FROM                			\r\n");
			strBuff.append("				TB_COMM_CD				\r\n");
			strBuff.append("		WHERE               			\r\n");
			strBuff.append("				CD = '0'				\r\n");
			
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		S	T	A	R	T");
			System.out.println("-----------------------------------------------------------------");
			System.out.println("대표코드만 조회\r\n" + strBuff.toString());
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		E	N	D	T");
			System.out.println("-----------------------------------------------------------------");
			
			pstmt = con.prepareStatement(strBuff.toString());

			rs = pstmt.executeQuery();

			while (rs.next()) {

				HashMap<String, String> unitlist = new HashMap<String, String>();

				unitlist.put("group_cd"				, rs.getString("GROUP_CD"));
				unitlist.put("group_code_nm"		, rs.getString("CD_NM"));
				
				group_cd = rs.getString("GROUP_CD");
				
				list.add(unitlist);

			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();

			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}
	
	/**
	 * @param group_code_nm
	 * @throws BaseException
	 */
	public void getGroupCodeName(String group_code_nm) throws BaseException {

		list = new ArrayList<HashMap<String, String>>();
		try {

			con = getDataSource().getConnection();
			
			strBuff.setLength(0);
			strBuff.append(" SELECT GROUP_CD,                 		\r\n");
			strBuff.append(" CD_NM                 					\r\n");
			strBuff.append(" FROM TB_COMM_CD                 		\r\n");
			strBuff.append(" WHERE CD = '0'                  		\r\n");
			strBuff.append(" AND CD_NM = '" + group_code_nm + "'    \r\n");
			
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		S	T	A	R	T");
			System.out.println("-----------------------------------------------------------------");
			System.out.println(strBuff.toString());
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		E	N	D	T");
			System.out.println("-----------------------------------------------------------------");

			pstmt = con.prepareStatement(strBuff.toString());

			rs = pstmt.executeQuery();

			while (rs.next()) {

				HashMap<String, String> unitlist = new HashMap<String, String>();

				unitlist.put("group_cd", rs.getString("GROUP_CD"));
				unitlist.put("group_code_nm", rs.getString("CD_NM"));
				group_cd = rs.getString("GROUP_CD");
				list.add(unitlist);

			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();

			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}
	
	public void getCode() throws BaseException {

		list = new ArrayList<HashMap<String, String>>();
		try {

			con = getDataSource().getConnection();
			strBuff.setLength(0);
			strBuff.append(" SELECT MAX(GROUP_CD)+1             \r\n");
			strBuff.append(" 		AS GROUP_CD	                \r\n");
			strBuff.append(" FROM	TB_COMM_CD                  \r\n");
			strBuff.append(" FROM	LEN(GROUP_CD)  > 2          \r\n");
			
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		S	T	A	R	T");
			System.out.println("-----------------------------------------------------------------");
			System.out.println(strBuff.toString());
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		E	N	D	T");
			System.out.println("-----------------------------------------------------------------");

			pstmt = con.prepareStatement(strBuff.toString());
			
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				
				groupCode = rs.getString("GROUP_CD");

			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();

			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}
	
	/**
	 * @param table
	 * @throws BaseException
	 */
	public void getCommonCode(String table) throws BaseException {

		list = new ArrayList<HashMap<String, String>>();
		
		try {
			
			String[] arr = table.split(",");
			
			for(int i = 0 ; i<arr.length; i++){
				if(i == 0){
					group_cd 	= 	arr[i];
				}else if(i == 1){
					cd 			= 	arr[i];
				}
			}

			con = getDataSource().getConnection();
			
			strBuff.setLength(0);
			
			strBuff.append("			SELECT * FROM TB_COMM_CD A,									\r\n");
			strBuff.append("			(                 											\r\n");
			strBuff.append("				SELECT GROUP_CD, CD_NM AS GROUP_CD_NM , use_YN YN		\r\n");
			strBuff.append("				FROM TB_COMM_CD 										\r\n");
			strBuff.append("				WHERE CD = '0'											\r\n");
			strBuff.append("			) B															\r\n");
			strBuff.append("			WHERE 1	=	1												\r\n");
			strBuff.append("			AND A.GROUP_CD	= B.GROUP_CD								\r\n");
			strBuff.append("			AND A.GROUP_CD	= '" + group_cd+"'							\r\n");
			strBuff.append("			AND A.CD		= '" + cd+"'								\r\n");
			
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		S	T	A	R	T");
			System.out.println("-----------------------------------------------------------------");
			System.out.println(strBuff.toString());
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		E	N	D	T");
			System.out.println("-----------------------------------------------------------------");
			
			pstmt = con.prepareStatement(strBuff.toString());
			
			rs = pstmt.executeQuery();

			while (rs.next()) {

				HashMap<String, String> hashtable = new HashMap<String, String>();
			
				hashtable.put("group_cd"		, rs.getString("GROUP_CD"));
				hashtable.put("group_code_nm"	, rs.getString("GROUP_CD_NM"));
				hashtable.put("cd"				, rs.getString("CD"));
				hashtable.put("full_nm"			, rs.getString("CD_NM"));
				hashtable.put("etc"				, rs.getString("ETC"));
				hashtable.put("use_yn"			, rs.getString("USE_YN"));
				hashtable.put("yn"				, rs.getString("yn"));

				list.add(hashtable);
			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();

			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}
	
	/**
	 * @param group_cd
	 * @param group_code_nm
	 * @throws BaseException
	 */
	public void getCommonCode(String group_cd, String group_code_nm) throws BaseException {

		list = new ArrayList<HashMap<String, String>>();
		try {
			
			con = getDataSource().getConnection();
			
			strBuff.setLength(0);
			
			strBuff.append(" SELECT * FROM TB_COMM_CD A,                 											\r\n");
			strBuff.append(" (                 																		\r\n");
			strBuff.append(" 	SELECT GROUP_CD, CD_NM AS GROUP_CD_NM FROM TB_COMM_CD WHERE CD = '0'                \r\n");
			strBuff.append(" ) B                 																	\r\n");
			strBuff.append(" WHERE 1=1                 																\r\n");
			strBuff.append(" AND A.GROUP_CD 	= B.GROUP_CD                   										\r\n");
			strBuff.append(" AND A.GROUP_CD 	= '" + group_cd + "'                 								\r\n");
			strBuff.append(" AND B.GROUP_CD_NM 	= '" + group_code_nm + "'                 							\r\n");
			
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		S	T	A	R	T");
			System.out.println("-----------------------------------------------------------------");
			System.out.println(strBuff.toString());
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		E	N	D	T");
			System.out.println("-----------------------------------------------------------------");
			
			pstmt = con.prepareStatement(strBuff.toString());
			
			rs = pstmt.executeQuery();

			while (rs.next()) {

				HashMap<String, String> hashtable = new HashMap<String, String>();
			
				hashtable.put("group_cd"			, rs.getString("GROUP_CD"));
				hashtable.put("group_code_nm"		, rs.getString("GROUP_CD_NM"));
				hashtable.put("cd"					, rs.getString("CD"));
				hashtable.put("full_nm"				, rs.getString("CD_NM"));
				hashtable.put("etc"					, rs.getString("ETC"));
				hashtable.put("use_yn"				, rs.getString("USE_YN"));

				list.add(hashtable);
			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();

			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}
	
	/**
	 * @param group_code_nm
	 * @throws BaseException
	 */
	public void existGroupCodeName(String group_code_nm) throws BaseException {

		list = new ArrayList<HashMap<String, String>>();
		try {
			con = getDataSource().getConnection();
			
			strBuff.setLength(0);
			
			strBuff.append("SELECT  COUNT(*) AS ROWNUM  FROM TB_COMM_CD A,                 						\r\n");
			strBuff.append(" (                 																	\r\n");
			strBuff.append(" SELECT GROUP_CD, CD_NM AS GROUP_CD_NM FROM TB_COMM_CD WHERE CD = '0'             	\r\n");
			strBuff.append(" ) B                 																\r\n");
			strBuff.append(" WHERE 1=1                															\r\n");
			strBuff.append(" AND A.GROUP_CD 		= B.GROUP_CD                   								\r\n");
			strBuff.append(" AND B.GROUP_CD_NM 		= '"+group_code_nm+"'                  						\r\n");
			

			pstmt = con.prepareStatement(strBuff.toString());
			
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		S	T	A	R	T");
			System.out.println("-----------------------------------------------------------------");
			System.out.println(strBuff.toString());
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		E	N	D	T");
			System.out.println("-----------------------------------------------------------------");

			rs = pstmt.executeQuery();

			while (rs.next()) {
				count = rs.getInt("ROWNUM");
			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();

			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}

	/**
	 * @param groupCode
	 * @param group_code_nm
	 * @param etc
	 * @param checkedUseyn
	 * @throws BaseException
	 */
	public void insertCommonCode(String groupCode, String group_code_nm, String etc, String checkedUseyn ) throws BaseException {
		
		System.out.println("!!!!!!!!!!!!!!!!!!!!  insertCommonCode  !!!!!!!!!!!!!!!!!!!!");
		System.out.println("!!!!!!!!!!!!!!!!!!!!  하위코드 추가  !!!!!!!!!!!!!!!!!!!!");
		System.out.println("===========================================================");
		
		System.out.println(groupCode);
		System.out.println(group_code_nm);
		System.out.println(etc);
		System.out.println(checkedUseyn);
		System.out.println("===========================================================");
		
		try {

			con = getDataSource().getConnection();
			
			strBuff.setLength(0);
			
			
			
			
			strBuff.setLength(0);
			
			strBuff.append("		INSERT INTO TB_COMM_CD (                \r\n");
			strBuff.append(" 			GROUP_CD,                 			\r\n");
			strBuff.append(" 			CD,                			 		\r\n");
			strBuff.append(" 			CD_NM,                				\r\n");
			strBuff.append(" 			ETC,                				\r\n");
			strBuff.append(" 			USE_YN	                			\r\n");
			strBuff.append(" 		) VALUES(                 				\r\n");
			strBuff.append(" 			'" + groupCode + "' ,               \r\n");
			strBuff.append(" 			'0',                 				\r\n");
			strBuff.append(" 			?,                 					\r\n");
			strBuff.append(" 			?,                 					\r\n");
			strBuff.append(" 			?	                 				\r\n");
			strBuff.append(" 		)	                 					\r\n");

			pstmt = con.prepareStatement(strBuff.toString());

			
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		S	T	A	R	T");
			System.out.println("-----------------------------------------------------------------");
			System.out.println(strBuff.toString());
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		E	N	D	T");
			System.out.println("-----------------------------------------------------------------");
			
			int idx = 1;
			pstmt.setString(idx++, group_code_nm );
			pstmt.setString(idx++, etc);
			pstmt.setString(idx++, checkedUseyn);
			
			result = pstmt.executeUpdate();

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();

			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}
	
	/**
	 * <pre>하위코드 추가및 중복확인</pre>
	 * @param group_cd
	 * @param cd
	 * @param full_nm
	 * @param nick_nm
	 * @param en_full_nm
	 * @param en_nick_nm
	 * @param etc
	 * @param checkedUseyn
	 * @param reg_id
	 * @throws BaseException
	 */
	public void insertSubCommonCode(String group_cd, String cd, String full_nm, String nick_nm , String etc, String checkedUseyn) throws BaseException {

		System.out.println(group_cd + "       group_cd");
		System.out.println(cd 		+ "                cd");
		System.out.println(full_nm 	+ "            full_nm");
		System.out.println(nick_nm 	+ "				nick_nm");
		System.out.println(etc 		+ "           		etc");
		System.out.println(checkedUseyn + "            checkedUseyn");

		try {

			con = getDataSource().getConnection();
			
			strBuff.setLength(0);
			
			strBuff.append("		SELECT  COUNT(*)        CNT									\r\n");
			strBuff.append("		FROM    TB_COMM_CD         									\r\n");
			strBuff.append("		WHERE   GROUP_CD        =  		'" + group_cd + "'			\r\n");
			strBuff.append("		AND     CD              =  		'" + cd + "'				\r\n");
			
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		S	T	A	R	T");
			System.out.println("-----------------------------------------------------------------");
			System.out.println(strBuff.toString());
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		E	N	D	T");
			System.out.println("-----------------------------------------------------------------");
			
			pstmt = con.prepareStatement(strBuff.toString());
			
			rs		=	pstmt.executeQuery();
			
			if (rs.next()) {
				result = rs.getInt("CNT");
				
			}
			
			/**
			 * 코드가 있다면 INSERT 하지 않는다.
			 */
			if (result > 0) {
				msg = CodeMetrix.DUPLICATE;
				
				System.out.println(msg + "코드 중복도미.");
			} else {
			
				strBuff.setLength(0);
				
				strBuff.append("		INSERT INTO TB_COMM_CD (    			\r\n");
				strBuff.append(" 			GROUP_CD,                 			\r\n");
				strBuff.append(" 			CD,                 				\r\n");
				strBuff.append(" 			CD_NM,                 				\r\n");
				strBuff.append(" 			ETC,                 				\r\n");
				strBuff.append(" 			USE_YN                 				\r\n");
				strBuff.append("  		) VALUES(                 				\r\n");
				strBuff.append(" 			? ,                					\r\n");
				strBuff.append(" 			?,                 					\r\n");
				strBuff.append(" 			?,                 					\r\n");
				strBuff.append(" 			?,                 					\r\n");
				strBuff.append(" 			?                 					\r\n");
				strBuff.append(" 		)                 						\r\n");
	
				
				System.out.println("-----------------------------------------------------------------");
				System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		S	T	A	R	T");
				System.out.println("-----------------------------------------------------------------");
				System.out.println(strBuff.toString());
				System.out.println("-----------------------------------------------------------------");
				System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		E	N	D	T");
				System.out.println("-----------------------------------------------------------------");
				
				pstmt 	= 	con.prepareStatement(strBuff.toString());
				
				int idx = 1;
				pstmt.setString(idx++, group_cd );
				pstmt.setString(idx++, cd);
				pstmt.setString(idx++, full_nm);
				pstmt.setString(idx++, etc );	
				pstmt.setString(idx++, checkedUseyn );
				
				result = pstmt.executeUpdate();
				
				if (result > 0) {
					msg = CodeMetrix.OK;
					System.out.println(msg + "성공 .");
				}
			}
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();

			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}
	
	/**
	 * @param group_cd
	 * @param cd
	 * @param use_yn
	 * @param reg_id
	 * @throws BaseException
	 */
	public void saveCommonCode(String group_cd, String cd, String use_yn, String reg_id) throws BaseException {
		
		try {
			
			con = getDataSource().getConnection();
			strBuff.setLength(0);
			strBuff.append(" UPDATE TB_COMM_CD SET                 \r\n");
			strBuff.append(" USE_YN = ?                 \r\n");
			strBuff.append(" WHERE GROUP_CD = '"+group_cd+"'                \r\n");
			strBuff.append(" AND CD = '"+cd+"'                \r\n");

			pstmt = con.prepareStatement(strBuff.toString());
			
			System.out.println("Update Query>>>>" + strBuff.toString());
			
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		S	T	A	R	T");
			System.out.println("-----------------------------------------------------------------");
			System.out.println(strBuff.toString());
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		E	N	D	T");
			System.out.println("-----------------------------------------------------------------");
						
			pstmt.setString(1, use_yn);
			pstmt.setString(2, reg_id );	
			pstmt.executeUpdate();
			
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();

			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}
	
	/**
	 * @param group_cd
	 * @param reg_id
	 * @throws BaseException
	 */
	public void saveGroupCode(String group_cd, String reg_id) throws BaseException {

		try {
			
			con = getDataSource().getConnection();
			
			strBuff.setLength(0);
			
			strBuff.append(" UPDATE TB_COMM_CD SET                 \r\n");
			strBuff.append(" USE_YN = 'N'                 			\r\n");
			strBuff.append(" WHERE GROUP_CD = '"+group_cd+"'                \r\n");

			pstmt = con.prepareStatement(strBuff.toString());
			
			System.out.println("Update Query>>>>" + strBuff.toString());
			
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		S	T	A	R	T");
			System.out.println("-----------------------------------------------------------------");
			System.out.println(strBuff.toString());
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		E	N	D	T");
			System.out.println("-----------------------------------------------------------------");
						
			pstmt.setString(1, reg_id );	
			pstmt.executeUpdate();
			
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();

			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}
	
	/**
	 * @param group_cd
	 * @param cd
	 * @throws BaseException
	 */
	public void deleteCommonCode(String group_cd, String cd) throws BaseException {

		list = new ArrayList<HashMap<String, String>>();
		try {

			con = getDataSource().getConnection();
			strBuff.setLength(0);
			strBuff.append(" DELETE FROM TB_COMM_CD                 \r\n");
			strBuff.append(" WHERE GROUP_CD = '"+group_cd+"'        \r\n");
			strBuff.append(" AND CD 		= '"+cd+"'                \r\n");
			
			System.out.println("Delete Query>>>>" + strBuff.toString());
			
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		S	T	A	R	T");
			System.out.println("-----------------------------------------------------------------");
			System.out.println(strBuff.toString());
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		E	N	D	T");
			System.out.println("-----------------------------------------------------------------");
			
			pstmt = con.prepareStatement(strBuff.toString());
			
			pstmt.executeUpdate();
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();

			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}
	
	/**
	 * @param group_cd
	 * @throws BaseException
	 */
	public void deleteSubCommonCode(String group_cd) throws BaseException {

		list = new ArrayList<HashMap<String, String>>();
		try {

			con = getDataSource().getConnection();
			
			strBuff.setLength(0);
			
			strBuff.append(" DELETE FROM TB_COMM_CD 			"	);
			strBuff.append(" WHERE GROUP_CD = '"+group_cd	+	"'"	);
			
			pstmt = con.prepareStatement(strBuff.toString());
			
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		S	T	A	R	T");
			System.out.println("-----------------------------------------------------------------");
			System.out.println(strBuff.toString());
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		E	N	D	T");
			System.out.println("-----------------------------------------------------------------");
			
			System.out.println("SubDelete Query>>>>" + strBuff.toString());
			
			pstmt.executeUpdate();
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();

			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}
	
	/**
	 * <pre>
	 * 대표코드 UPDATE
	 * 사용여부가 'N'이면 하위코드의 사용여부도 모두 'N'으로 처리한다.
	 * </pre>
	 * @param cd_nm		-	코드명
	 * @param use_yn	-	사용여부
	 * @param group_cd	-	대표코드
	 * @throws BaseException
	 */
	public void saveUpdateCommonCode(String cd_nm , String use_yn , String group_cd  , String etc) throws BaseException {
		
		try {

			con = getDataSource().getConnection(); //근태관리 Connection

			strBuff.setLength(0);
			
			//그룹의 사용여부가 'N' 이면 하위코드의 사용여부를 모두 'N'

			strBuff.append("					UPDATE      TB_COMM_CD                   				\r\n");
			strBuff.append("					SET                            							\r\n");
			strBuff.append("								CD_NM		=	?  							\r\n");
			strBuff.append("						,		USE_YN		=	?  							\r\n");
			strBuff.append("						,		ETC			=	'" + etc + "'  				\r\n");
			strBuff.append("					WHERE	1 = 1	                   						\r\n");
			strBuff.append("						AND		GROUP_CD	=	?  							\r\n");
			
			if (!use_yn.equals("N")) {
				strBuff.append("						AND		CD		=	'0'							\r\n");
			}


			System.out.println("======================================\r\n");
			System.out.println("대표코드 UPDATE 내용 \r\n사용여부가 'N'이면 하위코드의 사용여부도 모두 'N'으로 처리한다.\r\n" + strBuff.toString());
			System.out.println("======================================\r\n");

			pstmt = con.prepareStatement(strBuff.toString());
			
			/************** PARAMETERS START ****************************/

			int idx = 1;

			pstmt.setString(idx++	, 	cd_nm); 	//
			pstmt.setString(idx++	, 	use_yn); 	//
			pstmt.setString(idx++	, 	group_cd); //

			/************** PARAMETERS E N D ****************************/


			int row = pstmt.executeUpdate();

			if (row > 0) {
				msg = CodeMetrix.OK;
			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();
			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
		
	}


	/**
	 * <pre>하위공통코드 UPDATE</pre>
	 * @param cd			-	하위코드
	 * @param cd_nm			- 	코드명
	 * @param group_cd		-	상위코드
	 * @throws BaseException
	 */
	public void updateSubCommmonCode(String code , String cd , String cd_nm , String group_cd , String use_yn  , String etc) throws BaseException {
		System.out.println(cd);
		System.out.println(cd_nm);
		System.out.println(group_cd);
		
		try {

			con = getDataSource().getConnection(); //근태관리 Connection

			strBuff.setLength(0);

			strBuff.append("					SELECT	COUNT(*)       CNT 							\r\n");
			strBuff.append("					FROM	TB_COMM_CD        							\r\n");
			strBuff.append("					WHERE	CD			=	? 							\r\n");
			strBuff.append("						AND	GROUP_CD	=	? 							\r\n");

			System.out.println("======================================\r\n");
			System.out.println("쿼리실행 내용\r\n" + strBuff.toString());
			System.out.println("======================================\r\n");

			pstmt = con.prepareStatement(strBuff.toString());
			
			/************** PARAMETERS START ****************************/

			int idx = 1;
			
			pstmt.setString(idx++, code); //
			pstmt.setString(idx++, group_cd); //

			/************** PARAMETERS E N D ****************************/


			rs	=	pstmt.executeQuery();
			
			if (rs.next()) {
					//msg = CodeMetrix.DUPLICATE;
					
				strBuff.setLength(0);
				
				strBuff.append("					UPDATE                   							\r\n");
				strBuff.append("							TB_COMM_CD       							\r\n");
				strBuff.append("					SET													\r\n");
				strBuff.append("							CD_NM		=	'"+ cd_nm +"'				\r\n");
				strBuff.append("						,	CD			=	'"+ cd +"'					\r\n");
				strBuff.append("						,	ETC			=	'"+ etc +"'					\r\n");
				strBuff.append("						,	USE_YN		=	'"+ use_yn +"'				\r\n");
				strBuff.append("					WHERE                    							\r\n");
				strBuff.append("							GROUP_CD	=	'"+ group_cd +"'			\r\n");
				strBuff.append("						AND	CD			=	'"+ code +"'				\r\n");

				System.out.println("-----------------------------------------------------------------");
				System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		S	T	A	R	T");
				System.out.println("-----------------------------------------------------------------");
				System.out.println(strBuff.toString());
				System.out.println("-----------------------------------------------------------------");
				System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		E	N	D	T");
				System.out.println("-----------------------------------------------------------------");
				
				pstmt	=	con.prepareStatement(strBuff.toString());
				
				/************** PARAMETERS START ****************************/

				/************** PARAMETERS E N D ****************************/
				
				int n = pstmt.executeUpdate();
				
				if (n > 0) {
					setMsg(CodeMetrix.OK);
				}
			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();
			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
		
	}
	
	/**
	 * <pre> 대표코드    리스트</pre>
	 * @throws BaseException
	 * @since	2009/03/10
	 * @author Yang D
	 */
	public void retrieveGroupCodeList() throws BaseException {
		
		try {
		
			con = getDataSource().getConnection(); //근태관리 Connection
	
			strBuff.setLength(0);
	
			strBuff.append("					SELECT 	GROUP_CD , CD_NM 							\r\n");
			strBuff.append("					FROM 	TB_COMM_CD       							\r\n");
			strBuff.append("					WHERE 	CD = '0'         							\r\n");
	
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		S	T	A	R	T");
			System.out.println("-----------------------------------------------------------------");
			System.out.println("대표코드 조회\r\n" + strBuff.toString());
			System.out.println("-----------------------------------------------------------------");
			System.out.println("D	E	B	U	G	 	Q	U	E	R	Y		E	N	D	T");
			System.out.println("-----------------------------------------------------------------");
	
			pstmt = con.prepareStatement(strBuff.toString());
			
			/************** PARAMETERS START ****************************/
			/************** PARAMETERS E N D ****************************/
	
			rs	=	pstmt.executeQuery();
			
			list	=	new ArrayList<HashMap<String,String>>();
			
			while (rs.next()) {
				HashMap<String, String> hashtable = new HashMap<String, String>();
				
				hashtable.put("group_cd"	, rs.getString("GROUP_CD")	);
				hashtable.put("cd_nm"		, rs.getString("CD_NM")		);
	
				list.add(hashtable);
			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();
			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
		
	}
	
	/******************************************************************************************************************/

	public int getSearchResult() {
		return searchResult;
	}

	public void setSearchResult(int searchResult) {
		this.searchResult = searchResult;
	}

	public String getUse_yn() {
		return use_yn;
	}

	public void setUse_yn(String use_yn) {
		this.use_yn = use_yn;
	}

	public int getResult() {
		return result;
	}

	public void setResult(int result) {
		this.result = result;
	}

	public List<HashMap<String, String>> getList() {
		return list;
	}

	public void setList(List<HashMap<String, String>> list) {
		this.list = list;
	}

	public String getGroup_cd() {
		return group_cd;
	}

	public void setGroup_cd(String group_cd) {
		this.group_cd = group_cd;
	}

	public String getCd() {
		return cd;
	}

	public void setCd(String cd) {
		this.cd = cd;
	}

	public String getGroupCode() {
		return groupCode;
	}

	public void setGroupCode(String groupCode) {
		this.groupCode = groupCode;
	}

	public int getRowCount() {
		return rowCount;
	}

	public void setRowCount(int rowCount) {
		this.rowCount = rowCount;
	}

	public int getCount() {
		return count;
	}

	public void setCount(int count) {
		this.count = count;
	}

	public String getMsg() {
		return msg;
	}

	public void setMsg(String msg) {
		this.msg = msg;
	}


	/******************************************************************************************************************/
}
